import datetime
import json
from io import BytesIO

from flask import request, jsonify, Response
from openpyxl.workbook import Workbook
from sqlalchemy import func, and_, union, desc, text
from sqlalchemy.exc import IntegrityError

from tzq.document_management_system import dms_bp
from tzq.document_management_system.model.catalog_model import Catalog
from tzq.document_management_system.model.log_model import Log, Version
from tzq.document_management_system.model.permission_model import Permission
from tzq.extensions import db


# layui多条件分页查询接口
@dms_bp.route('/api/catalog', methods=['GET'])
def get_catalog():
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 10))

    principal = request.values.get("principal")
    online_date = request.values.get("online_date")
    created_by = request.values.get("created_by")
    # 获取搜索参数 - 业务字段
    parent_node_name = request.values.get("parent_node_name")
    name = request.values.get("name")
    description = request.values.get("description")
    url = request.values.get("url")
    order = request.values.get("order")
    open_link_type = request.values.get("open_link_type")

    query = Catalog.query
    query = query.filter(Catalog.delete_flag == 'N')
    if principal: query = query.filter(Catalog.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(Catalog.online_date == f'{online_date}')
    if created_by: query = query.filter(Catalog.created_by.ilike(f'%{created_by}%'))
    # 应用搜索条件 - 业务字段
    if parent_node_name: query = query.filter(Catalog.parent_node_name.ilike(f'%{parent_node_name}%'))
    if name: query = query.filter(Catalog.name.ilike(f'%{name}%'))
    if description: query = query.filter(Catalog.description.ilike(f'%{description}%'))
    if url: query = query.filter(Catalog.url.ilike(f'%{url}%'))
    if order: query = query.filter(Catalog.order.ilike(f'%{order}%'))
    if open_link_type: query = query.filter(Catalog.open_link_type.ilike(f'%{open_link_type}%'))

    sort_field = request.args.get('field', 'catalog_id')
    sort_order = request.args.get('order', 'desc')
    if hasattr(Catalog, sort_field):
        if sort_order == 'asc':
            order_by = getattr(Catalog, sort_field).asc()
        else:
            order_by = getattr(Catalog, sort_field).desc()
        query = query.order_by(order_by)
    else:
        query = query.order_by(Catalog.online_date.desc(), Catalog.catalog_id.desc())
    pagination = query.paginate(page=page, per_page=limit, error_out=False)
    return jsonify({
        'code': 0,
        'msg': '',
        'count': pagination.total,
        'data': [dataInfo.to_dict() for dataInfo in pagination.items]
    })


def check_ip_modify_permission(client_ip_i: str):
    # 检查客户端用户，是否有新增、修改、删除权限
    permission_temp = Permission.query.filter(
        and_(Permission.ip == client_ip_i, Permission.user_type.in_(['管理员', '读写用户']),
             Permission.delete_flag == 'N')).first()
    if not permission_temp:
        return False
    else:
        return True


def is_administrator(client_ip_i: str):
    # 检查用户IP是否为管理员
    permission_temp = Permission.query.filter(
        and_(Permission.ip == client_ip_i, Permission.user_type.in_(['管理员']),
             Permission.delete_flag == 'N')).first()
    if not permission_temp:
        return False
    else:
        return True


@dms_bp.route('/api/catalogUpdate', methods=['POST'])
def catalog_update_cell():
    # layui的table的单元格编辑，保存到后台
    try:
        # 判断是否有更新权限
        client_ip = request.remote_addr
        if not check_ip_modify_permission(client_ip):
            return jsonify({'status': 'error', 'message': f'你({client_ip})没有更新权限，请联系管理员twx1014920！'}), 200
        data = request.json
        instance_temp = Catalog.query.get(data['id'])
        if not instance_temp:
            return jsonify({'status': 'error', 'message': 'Data Info not found'}), 404
        # 判断该条信息是否是创建人，同时用户不能为管理员。
        if instance_temp.created_by != client_ip and not is_administrator(client_ip):
            return jsonify({'status': 'error', 'message': f'该记录不是你创建的，你无法修改！'}), 200

        v_old_value = ''
        v_new_value = data['value']

        if data['field'] == 'principal':
            v_old_value = instance_temp.principal
            instance_temp.principal = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        elif data['field'] == 'online_date':
            v_old_value = instance_temp.online_date.strftime('%Y-%m-%d')
            instance_temp.online_date = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        elif data['field'] == 'comment':
            v_old_value = instance_temp.comment
            instance_temp.comment = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        # 更新对应字段 - 业务字段
        if data['field'] == 'parent_node_name':
            v_old_value = instance_temp.parent_node_name
            instance_temp.parent_node_name = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        if data['field'] == 'name':
            v_old_value = instance_temp.name
            instance_temp.name = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        if data['field'] == 'description':
            v_old_value = instance_temp.description
            instance_temp.description = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        if data['field'] == 'url':
            v_old_value = instance_temp.url
            instance_temp.url = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        if data['field'] == 'order':
            v_old_value = instance_temp.order
            instance_temp.order = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip
        if data['field'] == 'open_link_type':
            v_old_value = instance_temp.open_link_type
            instance_temp.open_link_type = data['value']
            instance_temp.last_update_date = func.now()
            instance_temp.last_updated_by = client_ip

        log = Log(
            business_type='菜单',
            log_type='修改',
            ip=client_ip,
            field_name=data['field'],
            old_value=v_old_value,
            new_value=v_new_value,
        )
        db.session.add(log)
        db.session.commit()
        return jsonify({'status': 'success'})
    except Exception as e:
        db.session.rollback()
        return jsonify({'status': 'error', 'message': str(e)}), 500


@dms_bp.route('/api/addCatalog', methods=['POST'])
def add_catalog():
    # 添加数据接口
    data = request.get_json()
    client_ip = request.remote_addr
    # 判断是否有权限
    if not check_ip_modify_permission(client_ip):
        return jsonify({'status': 'error', 'message': f'你({client_ip})没有添加权限，请联系管理员twx1014920！'}), 200

    try:
        instance_temp = Catalog(
            # 业务字段
            parent_node_name=data['parent_node_name'],
            name=data['name'],
            description=data['description'],
            url=data['url'],
            order=data['order'],
            open_link_type=data['open_link_type'],

            principal=data['principal'],
            online_date=data['online_date_template'],
            comment=data['comment'],
            created_by=client_ip,
            last_updated_by=client_ip
        )
        db.session.add(instance_temp)
        db.session.commit()
        return jsonify({"code": 0, "msg": "添加成功"})
    except IntegrityError:
        return jsonify({"code": 1, "msg": "数据已存在"}), 400
    except Exception as e:
        return jsonify({"code": 2, "msg": str(e)}), 500


@dms_bp.route('/api/catalog/<int:id>', methods=['DELETE'])
def delete_catalog(id):
    # 软删除数据接口
    instance_temp = Catalog.query.get_or_404(id)
    try:
        # 判断是否有权限
        client_ip = request.remote_addr
        if not check_ip_modify_permission(client_ip):
            return jsonify({'status': 'error', 'message': f'你({client_ip})没有删除权限，请联系管理员twx1014920！'}), 200
        # 判断该条信息是否是创建人；
        if instance_temp.created_by != client_ip and not is_administrator(client_ip):
            return jsonify({'status': 'error', 'message': f'该记录不是你创建的，你无法删除！'}), 200
        v_old_value = json.dumps(instance_temp.to_dict())
        instance_temp.delete_flag = 'Y'
        instance_temp.last_update_date = func.now()
        instance_temp.last_updated_by = client_ip
        log = Log(
            business_type='菜单',
            log_type='删除',
            ip=client_ip,
            old_value=v_old_value,
        )
        db.session.add(log)
        db.session.commit()
        return jsonify({"code": 0, "msg": "删除成功"})
    except Exception as e:
        return jsonify({"code": 1, "msg": str(e)}), 500


@dms_bp.route('/api/exportCatalog', methods=['GET', 'POST'])
def export_catalog():
    # 数据导出
    principal = request.values.get("principal")
    online_date = request.values.get("online_date")
    created_by = request.values.get("created_by")
    # 获取搜索参数 - 业务字段
    parent_node_name = request.values.get("parent_node_name")
    name = request.values.get("name")
    description = request.values.get("description")
    url = request.values.get("url")
    order = request.values.get("order")
    open_link_type = request.values.get("open_link_type")

    query = Catalog.query
    query = query.filter(Catalog.delete_flag == 'N')
    if principal: query = query.filter(Catalog.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(Catalog.online_date == f'{online_date}')
    if created_by: query = query.filter(Catalog.created_by.ilike(f'%{created_by}%'))
    # 应用搜索条件 - 业务字段
    if parent_node_name: query = query.filter(Catalog.parent_node_name.ilike(f'%{parent_node_name}%'))
    if name: query = query.filter(Catalog.name.ilike(f'%{name}%'))
    if description: query = query.filter(Catalog.description.ilike(f'%{description}%'))
    if url: query = query.filter(Catalog.url.ilike(f'%{url}%'))
    if order: query = query.filter(Catalog.order.ilike(f'%{order}%'))
    if open_link_type: query = query.filter(Catalog.open_link_type.ilike(f'%{open_link_type}%'))

    query = query.order_by(Catalog.online_date.desc(), Catalog.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)

    wb = Workbook()
    ws = wb.active
    ws.title = "SiteNode"

    # 写入表头
    ws.append([
        # 表头 - 业务字段
        '父级菜单',
        '菜单',
        '说明',
        'url',
        '节点顺序',
        '窗口展示',

        'Last Update Date', '责任人', '上线日期'
    ])
    # 写入数据
    for item in query:
        ws.append([
            # 数据 - 业务字段
            item.parent_node_name,
            item.name,
            item.description,
            item.url,
            item.order,
            item.open_link_type,

            '',  # 最后更新时间可以不写，导入的时候就是当前时间了
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])

    date_string = datetime.datetime.now().strftime("%Y%m%d-%H%M%S")

    # 将工作簿写入 BytesIO 流
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    # 使用 Response 返回 Excel 文件
    return Response(
        output.getvalue(),
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={
            "Content-Disposition": "attachment; filename=" + date_string + "-jalor-catalog.xlsx"  # 文件名不能为中文，不然报错了
        }
    )


@dms_bp.route('/api/getCatalogOnlineDate', methods=['GET'])
def get_catalog_online_date():
    # 前端页面下拉框展示使用
    try:
        # 查询 delete_flag = 'N' 的 去重后 根据 online_date 倒叙排序的所有 online_date 的值
        data_list = db.session.query(Catalog.online_date.label('online_date')).filter(
            Catalog.delete_flag == 'N').distinct()
        subquery_b = db.session.query(Version.online_date.label('online_date')).distinct()
        union_query = union(data_list, subquery_b)
        result = db.session.execute(union_query.order_by(desc(text('online_date')))).fetchall()

        # 提取结果（因为返回的是元组列表）, 提取元组中的第一个元素
        online_date_data = [{"online_date": item[0].strftime('%Y-%m-%d')} for item in result]
        return jsonify({"code": 0, "msg": "", "count": len(online_date_data), "data": online_date_data})
    except Exception as e:
        print("Database error:", e)
        return jsonify({"code": -1, "msg": str(e), "count": 0, "data": None})
